﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CNLTHD_GoogleMapAPI_DTO;
using System.Data.OleDb;
namespace CNLTHD_GoogleMapAPI_DAO
{
	public class ChiaSeDiaDiemDAO
	{
		public static List<ChiaSeDiaDiem>LayDanhSachChiaSeNguoiNhan(int maNguoiDung)
		{				
			OleDbConnection ketNoi = ConnectingClass.KetNoi();

			if (ketNoi == null)
			{
				return null;
			}

			string truyVan = @"SELECT d.MaDiaDiem,
								c.MaChiaSe,
								g.MaNguoiDung, g.TenTaiKhoan, g.MatKhau, g.HoTen,
								n.MaNguoiDung, n.TenTaiKhoan, n.MatKhau, n.HoTen
								
								FROM NGUOI_DUNG g, NGUOI_DUNG n, CHIA_SE_DIA_DIEM c, DIA_DIEM d 
								WHERE c.MaNguoiChiaSe = g.MaNguoiDung AND c.MaNguoiNhan = n.MaNguoiDung
								AND c.MaDiaDiem = d.MaDiaDiem AND c.MaNguoiNhan = @MaNguoiNhan";
			OleDbParameter thamSoMaNguoiNhan = new OleDbParameter("@MaNguoiNhan", maNguoiDung);
			OleDbCommand cmd = new OleDbCommand(truyVan, ketNoi);
			cmd.Parameters.Add(thamSoMaNguoiNhan);

			List<ChiaSeDiaDiem> danhSach = new List<ChiaSeDiaDiem>();
			try
			{
				OleDbDataReader reader = cmd.ExecuteReader();
				
				while (reader.Read())
				{
					int maDiaDiem = reader.GetInt32(0);
					DiaDiem dd = DiaDiemDAO.LayDiaDiem(maDiaDiem);

					int maChiaSe = reader.GetInt32(1);

					int maNguoiNhan = reader.GetInt32(2);
					string tenTaiKhoanNhan = !reader.IsDBNull(3) ? reader.GetString(3) : "";
					string matKhauNguoiNhan = !reader.IsDBNull(4) ? reader.GetString(4) : "";
					string hoTenNguoiNhan = !reader.IsDBNull(5) ? reader.GetString(5) : "Chưa đặt tên";
					NguoiDung nguoiNhan= new NguoiDung { TenTaiKhoan = tenTaiKhoanNhan, MaNguoiDung = maNguoiNhan, HoTen = hoTenNguoiNhan, MatKhau = matKhauNguoiNhan };


					int maNguoiGui = reader.GetInt32(6);
					string tenTaiKhoanGui = !reader.IsDBNull(7) ? reader.GetString(7) : "";
					string matKhauNguoiGui = !reader.IsDBNull(8) ? reader.GetString(8) : "";
					string hoTenNguoiGui = !reader.IsDBNull(9) ? reader.GetString(9) : "Chưa đặt tên";
					NguoiDung nguoiGui = new NguoiDung { TenTaiKhoan = tenTaiKhoanGui, MaNguoiDung = maNguoiGui, HoTen = hoTenNguoiGui, MatKhau = matKhauNguoiGui };

					ChiaSeDiaDiem chiaSe = new ChiaSeDiaDiem
					{
						MaChiaSe = maChiaSe,
						NguoiChiaSe = nguoiGui,
						NguoiNhan = nguoiNhan,
						DiaDiem = dd
					};

					danhSach.Add(chiaSe);
				}
			}
			catch (System.Exception ex)
			{
				throw new Exception("Lấy danh sách  địa điểm chia sẻ bị lỗi");
				ketNoi.Close();
				return null;
			}
			finally
			{
			}

			ketNoi.Close();
		
			return danhSach;
		}

		public static List<ChiaSeDiaDiem> LayDanhSachChiaSeNguoiNhan(string tenTaiKhoan)
		{
			OleDbConnection ketNoi = ConnectingClass.KetNoi();

			if (ketNoi == null)
			{
				return null;
			}

			string truyVan = @"SELECT d.MaDiaDiem,
								c.MaChiaSe,
								g.MaNguoiDung, g.TenTaiKhoan, g.MatKhau, g.HoTen,
								n.MaNguoiDung, n.TenTaiKhoan, n.MatKhau, n.HoTen
								
								FROM NGUOI_DUNG g, NGUOI_DUNG n, CHIA_SE_DIA_DIEM c, DIA_DIEM d 
								WHERE c.MaNguoiChiaSe = g.MaNguoiDung AND c.MaNguoiNhan = n.MaNguoiDung
								AND c.MaDiaDiem = d.MaDiaDiem AND n.TenTaiKhoan = @TenTaiKhoan";
			OleDbParameter thamSoTenTaiKhoan = new OleDbParameter("@TenTaiKhoan", tenTaiKhoan);
			OleDbCommand cmd = new OleDbCommand(truyVan, ketNoi);
			cmd.Parameters.Add(thamSoTenTaiKhoan);

			List<ChiaSeDiaDiem> danhSach = new List<ChiaSeDiaDiem>();
			try
			{
				OleDbDataReader reader = cmd.ExecuteReader();

				while (reader.Read())
				{
					int maDiaDiem = reader.GetInt32(0);
					DiaDiem dd = DiaDiemDAO.LayDiaDiem(maDiaDiem);

					int maChiaSe = reader.GetInt32(1);

					int maNguoiNhan = reader.GetInt32(2);
					string tenTaiKhoanNhan = !reader.IsDBNull(3) ? reader.GetString(3) : "";
					string matKhauNguoiNhan = !reader.IsDBNull(4) ? reader.GetString(4) : "";
					string hoTenNguoiNhan = !reader.IsDBNull(5) ? reader.GetString(5) : "Chưa đặt tên";
					NguoiDung nguoiNhan = new NguoiDung { TenTaiKhoan = tenTaiKhoanNhan, MaNguoiDung = maNguoiNhan, HoTen = hoTenNguoiNhan, MatKhau = matKhauNguoiNhan };


					int maNguoiGui = reader.GetInt32(6);
					string tenTaiKhoanGui = !reader.IsDBNull(7) ? reader.GetString(7) : "";
					string matKhauNguoiGui = !reader.IsDBNull(8) ? reader.GetString(8) : "";
					string hoTenNguoiGui = !reader.IsDBNull(9) ? reader.GetString(9) : "Chưa đặt tên";
					NguoiDung nguoiGui = new NguoiDung { TenTaiKhoan = tenTaiKhoanGui, MaNguoiDung = maNguoiGui, HoTen = hoTenNguoiGui, MatKhau = matKhauNguoiGui };

					ChiaSeDiaDiem chiaSe = new ChiaSeDiaDiem
					{
						MaChiaSe = maChiaSe,
						NguoiChiaSe = nguoiGui,
						NguoiNhan = nguoiNhan,
						DiaDiem = dd
					};

					danhSach.Add(chiaSe);
				}
			}
			catch (System.Exception ex)
			{
				throw new Exception("Lấy danh sách  địa điểm chia sẻ bị lỗi");
				ketNoi.Close();
				return null;
			}
			finally
			{
			}

			ketNoi.Close();

			return danhSach;
		}

		public static List<ChiaSeDiaDiem> LayDanhSachChiaSeNguoiNhan(NguoiDung nd)
		{
			if (nd == null)
			{
				return null;
			}

			return LayDanhSachChiaSeNguoiNhan(nd.MaNguoiDung);
		}

		public static List<ChiaSeDiaDiem> LayDanhSachChiaSeNguoiChiaSe(int maNguoiDung)
		{
			OleDbConnection ketNoi = ConnectingClass.KetNoi();

			if (ketNoi == null)
			{
				return null;
			}

			string truyVan = @"SELECT d.MaDiaDiem,
								c.MaChiaSe,
								g.MaNguoiDung, g.TenTaiKhoan, g.MatKhau, g.HoTen,
								n.MaNguoiDung, n.TenTaiKhoan, n.MatKhau, n.HoTen
								
								FROM NGUOI_DUNG g, NGUOI_DUNG n, CHIA_SE_DIA_DIEM c, DIA_DIEM d 
								WHERE c.MaNguoiChiaSe = g.MaNguoiDung AND c.MaNguoiNhan = n.MaNguoiDung
								AND c.MaDiaDiem = d.MaDiaDiem AND c.MaNguoiChiaSe = @MaNguoiChiaSe";
			OleDbParameter thamSoMaNguoiNhan = new OleDbParameter("@MaNguoiChiaSe", maNguoiDung);
			OleDbCommand cmd = new OleDbCommand(truyVan, ketNoi);
			cmd.Parameters.Add(thamSoMaNguoiNhan);

			List<ChiaSeDiaDiem> danhSach = new List<ChiaSeDiaDiem>();
			try
			{
				OleDbDataReader reader = cmd.ExecuteReader();

				while (reader.Read())
				{
					int maDiaDiem = reader.GetInt32(0);
					DiaDiem dd = DiaDiemDAO.LayDiaDiem(maDiaDiem);

					int maChiaSe = reader.GetInt32(1);

					int maNguoiNhan = reader.GetInt32(2);
					string tenTaiKhoanNhan = !reader.IsDBNull(3) ? reader.GetString(3) : "";
					string matKhauNguoiNhan = !reader.IsDBNull(4) ? reader.GetString(4) : "";
					string hoTenNguoiNhan = !reader.IsDBNull(5) ? reader.GetString(5) : "Chưa đặt tên";
					NguoiDung nguoiNhan = new NguoiDung { TenTaiKhoan = tenTaiKhoanNhan, MaNguoiDung = maNguoiNhan, HoTen = hoTenNguoiNhan, MatKhau = matKhauNguoiNhan };


					int maNguoiGui = reader.GetInt32(6);
					string tenTaiKhoanGui = !reader.IsDBNull(7) ? reader.GetString(7) : "";
					string matKhauNguoiGui = !reader.IsDBNull(8) ? reader.GetString(8) : "";
					string hoTenNguoiGui = !reader.IsDBNull(9) ? reader.GetString(9) : "Chưa đặt tên";
					NguoiDung nguoiGui = new NguoiDung { TenTaiKhoan = tenTaiKhoanGui, MaNguoiDung = maNguoiGui, HoTen = hoTenNguoiGui, MatKhau = matKhauNguoiGui };

					ChiaSeDiaDiem chiaSe = new ChiaSeDiaDiem
					{
						MaChiaSe = maChiaSe,
						NguoiChiaSe = nguoiGui,
						NguoiNhan = nguoiNhan,
						DiaDiem = dd
					};

					danhSach.Add(chiaSe);
				}
			}
			catch (System.Exception ex)
			{
				throw new Exception("Lấy danh sách  địa điểm chia sẻ bị lỗi");
				ketNoi.Close();
				return null;
			}
			finally
			{
			}

			ketNoi.Close();

			return danhSach;
		}

		public static List<ChiaSeDiaDiem> LayDanhSachChiaSeNguoiChiaSe(string tenTaiKhoan)
		{
			OleDbConnection ketNoi = ConnectingClass.KetNoi();

			if (ketNoi == null)
			{
				return null;
			}

			string truyVan = @"SELECT d.MaDiaDiem,
								c.MaChiaSe,
								g.MaNguoiDung, g.TenTaiKhoan, g.MatKhau, g.HoTen,
								n.MaNguoiDung, n.TenTaiKhoan, n.MatKhau, n.HoTen
								
								FROM NGUOI_DUNG g, NGUOI_DUNG n, CHIA_SE_DIA_DIEM c, DIA_DIEM d 
								WHERE c.MaNguoiChiaSe = g.MaNguoiDung AND c.MaNguoiNhan = n.MaNguoiDung
								AND c.MaDiaDiem = d.MaDiaDiem AND g.TenTaiKhoan = @TenTaiKhoan";
			OleDbParameter thamSoTenTaiKhoan = new OleDbParameter("@TenTaiKhoan", tenTaiKhoan);
			OleDbCommand cmd = new OleDbCommand(truyVan, ketNoi);
			cmd.Parameters.Add(thamSoTenTaiKhoan);

			List<ChiaSeDiaDiem> danhSach = new List<ChiaSeDiaDiem>();
			try
			{
				OleDbDataReader reader = cmd.ExecuteReader();

				while (reader.Read())
				{
					int maDiaDiem = reader.GetInt32(0);
					DiaDiem dd = DiaDiemDAO.LayDiaDiem(maDiaDiem);

					int maChiaSe = reader.GetInt32(1);

					int maNguoiNhan = reader.GetInt32(2);
					string tenTaiKhoanNhan = !reader.IsDBNull(3) ? reader.GetString(3) : "";
					string matKhauNguoiNhan = !reader.IsDBNull(4) ? reader.GetString(4) : "";
					string hoTenNguoiNhan = !reader.IsDBNull(5) ? reader.GetString(5) : "Chưa đặt tên";
					NguoiDung nguoiNhan = new NguoiDung { TenTaiKhoan = tenTaiKhoanNhan, MaNguoiDung = maNguoiNhan, HoTen = hoTenNguoiNhan, MatKhau = matKhauNguoiNhan };


					int maNguoiGui = reader.GetInt32(6);
					string tenTaiKhoanGui = !reader.IsDBNull(7) ? reader.GetString(7) : "";
					string matKhauNguoiGui = !reader.IsDBNull(8) ? reader.GetString(8) : "";
					string hoTenNguoiGui = !reader.IsDBNull(9) ? reader.GetString(9) : "Chưa đặt tên";
					NguoiDung nguoiGui = new NguoiDung { TenTaiKhoan = tenTaiKhoanGui, MaNguoiDung = maNguoiGui, HoTen = hoTenNguoiGui, MatKhau = matKhauNguoiGui };

					ChiaSeDiaDiem chiaSe = new ChiaSeDiaDiem
					{
						MaChiaSe = maChiaSe,
						NguoiChiaSe = nguoiGui,
						NguoiNhan = nguoiNhan,
						DiaDiem = dd
					};

					danhSach.Add(chiaSe);
				}
			}
			catch (System.Exception ex)
			{
				throw new Exception("Lấy danh sách  địa điểm chia sẻ bị lỗi");
				ketNoi.Close();
				return null;
			}
			finally
			{
			}

			ketNoi.Close();

			return danhSach;
		}

		public static List<ChiaSeDiaDiem> LayDanhSachChiaSeNguoiChiaSe(NguoiDung nd)
		{
			if (nd == null)
			{
				return null;
			}

			return LayDanhSachChiaSeNguoiChiaSe(nd.MaNguoiDung);
		}


		public static bool ThemChiaSeDiaDiem(int maNguoiChiaSe, int maNguoiNhan, int maDiaDiem)
		{
			OleDbConnection ketNoi = ConnectingClass.KetNoi();

			if (ketNoi == null)
			{
				return false;
			}

			string truyVan = @"INSERT INTO CHIA_SE_DIA_DIEM(MaNguoiNhan, MaNguoiChiaSe, MaDiaDiem)
								VALUES(@MaNguoiNhan, @MaNguoiChiaSe, @MaDiaDiem)";
			OleDbParameter thamSoMaNguoiNhan = new OleDbParameter("@MaNguoiNhan", maNguoiNhan);
			OleDbParameter thamSoMaNguoiChiaSe = new OleDbParameter("@MaNguoiChiaSe", maNguoiChiaSe);
			OleDbParameter thamSoMaDiaDiem = new OleDbParameter("@MaDiaDiem", maDiaDiem);
			
			OleDbCommand cmd = new OleDbCommand(truyVan, ketNoi);
			cmd.Parameters.Add(thamSoMaNguoiNhan);
			cmd.Parameters.Add(thamSoMaNguoiChiaSe);
			cmd.Parameters.Add(thamSoMaDiaDiem);

			try
			{
				int result = cmd.ExecuteNonQuery();

				if (result >= 1)
				{
					return true;
				}
				else
				{
					return false;
				}
			}
			catch (System.Exception ex)
			{
				throw new Exception("Thêm chia sẽ đia điểm bị lỗi");
				ketNoi.Close();
				return false;
			}
			finally
			{
			}

			ketNoi.Close();

			return true;
		}

		public static bool XoaChiaSe(int maDiaDiem)
		{
			OleDbConnection ketNoi = ConnectingClass.KetNoi();

			if (ketNoi == null)
			{
				return false;
			}
			string truyVan = @"DELETE FROM CHIA_SE_DIA_DIEM
								 WHERE MaDiaDiem = @MaDiaDiem";
			OleDbParameter thamSoMaDiaDiem = new OleDbParameter("@MaDiaDiem", maDiaDiem);

			OleDbCommand cmd = new OleDbCommand(truyVan, ketNoi);
			cmd.Parameters.Add((thamSoMaDiaDiem));

			try
			{
				int result = cmd.ExecuteNonQuery();

				if (result >= 1)
				{
					return true;
				}
				else
				{
					return false;
				}
			}
			catch (System.Exception ex)
			{
				throw new Exception("Xóa chia sẽ đia điểm bị lỗi");
				ketNoi.Close();
				return false;
			}
			finally
			{
			}

			ketNoi.Close();
			return true;
		}
	}
}
